﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using SapB1WebPOS.Utils;
using System.Text;

namespace SapB1WebPOS.DAL
{
    public class ItemMasterDataDAO
    {
        public DataSet GetTop1OITM(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet("OITM");
                SQLConnection connect = new SQLConnection(companyDB);

                StringBuilder query = new StringBuilder();

                query.Append("SELECT TOP 1 ItemCode FROM OITM ");
                dt = connect.Excute_Query(query.ToString(), null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOITM(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet("OITM");
                SQLConnection connect = new SQLConnection(companyDB);

                StringBuilder query = new StringBuilder();
                query.Append("SELECT ISNULL(U_WebPOSPrice,'Y') AS U_WebPOSPrice, CASE WHEN T0.DfltWH IS NULL THEN (SELECT DfltWhs FROM OADM) ELSE T0.DfltWH END AS DfltWh,T0.ItemCode, ItemName, FrgnName, ItmsGrpCod,InvntryUom, 0 AS Price,VatGourpSa,VatGroupPu FROM OITM T0");
                query.Append(" ORDER BY T0.ItemCode ASC  ");
                dt = connect.Excute_Query(query.ToString(), null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOITM(string companyDB, string keySearch)
        {
            try
            {
                DataSet dt = new DataSet("OITM");
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(keySearch);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT ISNULL(U_WebPOSPrice,'Y') AS U_WebPOSPrice,CASE WHEN T0.DfltWH IS NULL THEN (SELECT DfltWhs FROM OADM) ELSE T0.DfltWH END AS DfltWh,T0.ItemCode, ItemName, FrgnName, ItmsGrpCod,InvntryUom, 0 AS Price,VatGourpSa,VatGroupPu FROM OITM T0 ");
                query.Append(" WHERE  T0.ItemCode LIKE '%' + @Param1 + '%' OR T0.ItemName LIKE '%' + @Param1 + '%'  ");
                query.Append(" ORDER BY T0.ItemCode ASC  ");

                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOITMByItemCode(string companyDB, string itemCode)
        {
            try
            {
                DataSet dt = new DataSet("OITM");
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParam = new List<object>();
                lstParam.Add(itemCode);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT ISNULL(U_WebPOSPrice,'Y') AS U_WebPOSPrice,CASE WHEN T0.DfltWH IS NULL THEN (SELECT DfltWhs FROM OADM) ELSE T0.DfltWH END AS DfltWh,T0.ItemCode, ItemName, FrgnName, ItmsGrpCod,InvntryUom, 0  AS Price,VatGourpSa,VatGroupPu FROM OITM T0 ");
                query.Append(" WHERE T0.ItemCode = @Param1 ");
                query.Append(" ORDER BY T0.ItemCode ASC  ");

                dt = connect.Excute_Query(query.ToString(), lstParam);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public long CountOITM(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet("OITM");
                SQLConnection connect = new SQLConnection(companyDB);
                dt = connect.Excute_Query("SELECT COUNT(*) AS NumOfBP FROM OITM", null);
                if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
                {
                    string numOfBP = dt.Tables[0].Rows[0]["NumOfBP"].ToString();
                    if (numOfBP.Length > 0)
                    {
                        return long.Parse(numOfBP);
                    }
                }
                return 0;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
    }
}